﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace Utilities
{
    public class SqlHelper
    {


        private static string connectionString = string.Empty;
        public static string GetConnectionString()
        {
            if (connectionString == string.Empty)
            {
                connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            }
            return connectionString;
        }
        public static SqlDataReader executeReader(string sql)
        {
            SqlDataReader sdr = null;
            SqlCommand cmd = null;
            SqlConnection conn = null;
            string source = GetConnectionString();
            conn = new SqlConnection(source);
            try
            {
                conn.Open();
                cmd = new SqlCommand(sql, conn);
                cmd.CommandTimeout = 0;
                sdr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (sdr != null) sdr.Dispose();
                conn.Close();
            }
            return sdr;
        }
        public static DataSet executeQuery(string sql)
        {
            SqlDataAdapter adptr = null;
            DataSet ds = null;
            string source = GetConnectionString();
            SqlConnection conn;
            conn = new SqlConnection(source);
            try
            {
                conn.Open();
                adptr = new SqlDataAdapter(sql, conn);
                ds = new DataSet();
                adptr.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (ds != null) ds.Dispose();
                if (adptr != null) adptr.Dispose();
                conn.Close();
            }
            return ds;
        }
        public static int executeExists(string sql)
        {
            int resume = 0;
            SqlCommand cmd = null;
            SqlConnection conn = null;
            string source = GetConnectionString();
            conn = new SqlConnection(source);
            try
            {
                conn.Open();
                cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                resume = (int)(cmd.Parameters["@RETURN_VALUE"].Value);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                conn.Close();
            }
            return resume;
        }
        public static int executeNonQuery(string sql)
        {
            string source = GetConnectionString();
            SqlConnection conn;
            SqlCommand cmd = null;
            int ret = -1;
            conn = new SqlConnection(source);
            try
            {
                // Open the connection                
                conn.Open();
                cmd = new SqlCommand(sql, conn);
                ret = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                conn.Close();
            }
            return ret;
        }
        public static bool ExcuteWithSqlConnection(string storedProcedureName, ArrayList Parameters, SqlConnection cnn, SqlTransaction trans)
        {
            int n = 0;
            SqlCommand command;

            try
            {
                // Open the connection                                
                command = new SqlCommand(storedProcedureName, cnn, trans);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 0;
                command.Parameters.Clear();
                n = Parameters.Count;
                for (int i = 0; i < n; i++)
                    command.Parameters.Add((SqlParameter)Parameters[i]);
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            return true;

        }

        public static void ExecSelectCommand(string storedProcedureName, out SqlDataAdapter adapter)
        {
            //
            adapter = new SqlDataAdapter();
            string source = GetConnectionString();
            SqlConnection conn;
            SqlCommand command = new SqlCommand();
            conn = new SqlConnection(source);
            try
            {
                // Open the connection   
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                command = new SqlCommand(storedProcedureName, conn);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 0;
                adapter = new SqlDataAdapter(command);

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static void DoStoredProcedure(string storedProcedureName, ArrayList Parameters, out SqlDataAdapter adapter)
        {
            int n = 0;
            adapter = new SqlDataAdapter();
            string source = GetConnectionString();
            SqlConnection conn;
            SqlCommand command = new SqlCommand();
            conn = new SqlConnection(source);
            try
            {
                // Open the connection   
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                command = new SqlCommand(storedProcedureName, conn);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 0;
                command.Parameters.Clear();
                n = Parameters.Count;
                for (int i = 0; i < n; i++)
                    command.Parameters.Add((SqlParameter)Parameters[i]);
                adapter = new SqlDataAdapter(command);
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                command.Connection.Close();
            }
        }
        public static DataSet executeStore(List<string> cols, Dictionary<string, string> keyValue, Dictionary<string, SqlDbType> keyType, string tableName)
        {
            SqlCommand cmd = null;
            SqlDataAdapter adptr = null;
            DataSet ds = null;
            string source = GetConnectionString();
            SqlConnection conn;
            conn = new SqlConnection(source);
            try
            {
                conn.Open();
                int colIdx = 0;
                cmd = new SqlCommand();
                StringBuilder sql = new StringBuilder();
                sql.Append("select ");
                foreach (string col in cols)
                {
                    if (colIdx.Equals(0))
                    {
                        sql.Append(col + " ");
                    }
                    else
                    {
                        sql.Append(", " + col + " ");
                    }
                    ++colIdx;
                }

                sql.Append("from " + tableName + " ");
                if ((keyValue != null) && (keyType != null))
                {
                    sql.Append("where 0=0 ");
                    foreach (string key in keyValue.Keys)
                    {
                        sql.Append("and " + key + " = @" + key + " ");
                        // varchar , char
                        if (keyType[key].Equals(SqlDbType.Char) || keyType[key].Equals(SqlDbType.VarChar) || keyType[key].Equals(SqlDbType.NVarChar))
                        {
                            cmd.Parameters.Add("@" + key, keyType[key]).Value = keyValue[key];
                        }
                        // int , tinyint
                        else if (keyType[key].Equals(SqlDbType.Int) || keyType[key].Equals(SqlDbType.TinyInt))
                        {
                            cmd.Parameters.Add("@" + key, keyType[key]).Value = int.Parse(keyValue[key]);
                        }
                        // datetime
                        else if (keyType[key].Equals(SqlDbType.DateTime))
                        {
                            cmd.Parameters.Add("@" + key, keyType[key]).Value = DateTime.Parse(keyValue[key]);
                        }
                    }
                }
                cmd.Connection = conn;
                cmd.CommandText = sql.ToString();
                adptr = new SqlDataAdapter(cmd);
                ds = new DataSet();
                adptr.Fill(ds, tableName);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (adptr != null) adptr.Dispose();
                if (cmd != null) cmd.Dispose();
                conn.Close();
            }
            return ds;
        }
        public static DataSet executeData(string storename, ArrayList Parameters)
        {
            int n = 0;
            SqlDataAdapter adptr = null;
            SqlCommand cmd = null;
            DataSet ds = null;
            SqlConnection conn;
            string source = GetConnectionString();
            conn = new SqlConnection(source);
            try
            {
                conn.Open();
                cmd = new SqlCommand(storename, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandTimeout = 0;
                cmd.Parameters.Clear();
                if (Parameters != null)
                {
                    n = Parameters.Count;
                    for (int i = 0; i < n; i++)
                        cmd.Parameters.Add((SqlParameter)Parameters[i]);
                }
                adptr = new SqlDataAdapter(cmd);
                ds = new DataSet();
                adptr.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (ds != null) ds.Dispose();
                if (cmd != null) cmd.Dispose();
                if (adptr != null) adptr.Dispose();
                conn.Close();
            }
            return ds;
        }
    }
}
